package cs.uns.edu.ar.aygps.model;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import cs.uns.edu.ar.aygps.db.ConnectionFactory;

public class AyGPSInstaller {
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		createDatabase();
		populateDatabase();
	  }

	private static void createDatabase() {
		Statement ps = null;
		Connection con = null;
		try {
			System.out.println("Generando la base de datos...");
			con = ConnectionFactory.getInstance().getConnection();
			FileInputStream fstream = new FileInputStream("/AyGPS/AyGPS-Data/aygps_ddl.sql");
		    // Get the object of DataInputStream
		    DataInputStream in = new DataInputStream(fstream);
		    BufferedReader br = new BufferedReader(new InputStreamReader(in));
		    String ddlLine;
		    //Read File Line By Line
		    while ((ddlLine = br.readLine()) != null)  {
		    	if (ddlLine.startsWith("CREATE") || ddlLine.startsWith("ALTER")){
		    		ps = con.createStatement();
		    		ps.execute(ddlLine);
		    	}
		    }	
		    
		    Connection conn=null;
			conn = ConnectionFactory.getInstance().getConnection();
			PreparedStatement resStm = conn.prepareStatement("INSERT INTO CLIENTE (DNI, NOMBRE) VALUES (0, 'ELIMINADO')");
			resStm.execute();	
		    	    
			if (ps != null)
				ps.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} 	
	}
	
	private static final String STORE_PROVINCIA = "INSERT INTO APP.PROVINCIA VALUES (? , ?)";
	private static final String STORE_CIUDAD = "INSERT INTO APP.LOCALIDAD VALUES (? , ?)";
	private static int pId = 0;
	private static int cId = 0;
	private static void persistProvinciaOrCiudad(String strLine, Connection con) {
		PreparedStatement ps = null;
		try {
			
			
			
		if (strLine.startsWith("p")){
			System.out.println("Cargando provincia: " + strLine.substring(2));			
			ps = con.prepareStatement(STORE_PROVINCIA);
			ps.setInt(1, pId++);
			ps.setString(2, strLine.substring(2));
		}
		if (strLine.startsWith("c")){
			System.out.println("Cargando ciudad: " + strLine.substring(2));
			ps = con.prepareStatement(STORE_CIUDAD);
			ps.setInt(1, cId++);
			ps.setString(2, strLine.substring(2));
		}
		if (ps!=null){
			ps.execute();
			ps.close();
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	private static void populateDatabase(){
		try{
			System.out.println("Cargando provincias y ciudades...");
			
			
			FileInputStream fstream = new FileInputStream("/AyGPS/AyGPS-Data/ciudades2.txt");
		    // Get the object of DataInputStream
		    DataInputStream in = new DataInputStream(fstream);
		        BufferedReader br = new BufferedReader(new InputStreamReader(in));
		    String strLine;
		    //Read File Line By Line
		    while ((strLine = br.readLine()) != null)   {
		    	persistProvinciaOrCiudad(strLine,ConnectionFactory.getInstance().getConnection());
		    }
		    //Close the input stream
		    in.close();
	    } catch (Exception e){//Catch exception if any
	    	System.err.println("Error: " + e.getMessage());
	    	e.printStackTrace();
	    }
	}

}

